﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using MySql.Data.MySqlClient;
using OfficeOpenXml;
using MySqlX.XDevAPI;
using LicenseContext = OfficeOpenXml.LicenseContext;

namespace student_systemApp
{
    public partial class ListStudentForm : Form
    {
        public ListStudentForm()
        {
            InitializeComponent();
        }

        private void ListStudentForm_Load(object sender, EventArgs e)
        {
            //初始化学生数据
            initStudent();
            //初始化班级数据
            initClass();
        }

        /**
         * 
         * 修改、删除学生数据
         */
        private void dgv_listStudent_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            DataGridViewCell cell = dgv_listStudent.CurrentCell;
            int rowIndex = cell.RowIndex;
            //获取选中列的student_id
            string studentId = dgv_listStudent.Rows[rowIndex].Cells["student_id"].Value.ToString();
            if (cell != null)
            {
                //判断修改还是删除
                if (dgv_listStudent.Columns[e.ColumnIndex].Name == "Column_update")
                {
                    //打开修改窗体
                    UpdateStudentForm updateStudentForm = new UpdateStudentForm();
                    updateStudentForm.Tag = studentId;
                    updateStudentForm.MdiParent = this.MdiParent;
                    pullMdiForm(updateStudentForm);
                    updateStudentForm.Show();
                    this.Hide();
                }
            }
            if (dgv_listStudent.Columns[e.ColumnIndex].Name == "Column_delete")
            {
                if (MessageBox.Show("确定要删除该学生吗？", "提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    //删除学生数据
                    string connectionString = ConfigurationManager.ConnectionStrings["UserManagementConnectionString"].ConnectionString;
                    string sql = "DELETE FROM student WHERE student_id = @studentId";
                    using (var connection = new MySqlConnection(connectionString))
                    {
                        try
                        {
                            connection.Open();
                            MySqlCommand mySqlCommand = new MySqlCommand(sql, connection);
                            mySqlCommand.Parameters.AddWithValue("@studentId", studentId);
                            int result = mySqlCommand.ExecuteNonQuery();
                            if (result > 0)
                            {
                                MessageBox.Show("删除成功", "提示");
                                //刷新数据
                                initStudent();
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("删除失败" + ex.Message, "提示");
                        }
                        connection.Close();
                    }
                }
            }
        }


        /**
         * 学生数据
         */

        private void initStudent()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["UserManagementConnectionString"].ConnectionString;
            string sql = "SELECT " +
                "s.student_id," +
                "s.student_name," +
                "s.sex," +
                "s.phone_number," +
                "c.class_name," +
                "g.grade_name " +
                "FROM" +
                " student s " +
                "INNER JOIN  " +
                "class c ON s.class_id = c.class_id" +
                " INNER JOIN " +
                "grade g ON s.grade_id = g.grade_id";

            // 构建动态WHERE子句
            List<string> conditions = new List<string>();
            if (comboBox_class.SelectedIndex > 0)
            {
                conditions.Add("s.class_id = @classId");
            }
            if (!string.IsNullOrEmpty(textBox_studentName.Text.Trim()))
            {
                conditions.Add("student_name LIKE @studentName");
            }
            // 如果有条件，则添加WHERE子句
            if (conditions.Count > 0)
            {
                sql += " WHERE " + string.Join(" AND ", conditions);
            }

            sql += " ORDER BY student_id ASC;";
            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(sql, connection);
                    mySqlCommand.Parameters.AddWithValue("@classId", comboBox_class.SelectedValue);
                    mySqlCommand.Parameters.AddWithValue("@studentName", "%" + textBox_studentName.Text + "%");

                    connection.Open();
                    var adapter = new MySqlDataAdapter(mySqlCommand);
                    var dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    foreach (DataRow row in dataTable.Rows)
                    {
                        if (row["sex"].ToString() == "M")
                        {
                            row["sex"] = "男";
                        }
                        else if (row["sex"].ToString() == "F")
                        {
                            row["sex"] = "女";
                        }
                    }

                    // 将 DataTable 绑定到 DataGridView 控件
                    dgv_listStudent.DataSource = dataTable;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查找失败" + ex.Message, "提示");
                }
                connection.Close();
            }
        }
        /**
         * 初始化班级下拉框
         */
        public void initClass()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["UserManagementConnectionString"].ConnectionString;
            string sql = "SELECT class_id,class_name,grade_name from" +
                " class c " +
                "inner join" +
                " grade g on c.grade_id = g.grade_id" +
                " order by g.grade_id ASC";

            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    MySqlCommand command = new MySqlCommand(sql, connection);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);

                    DataRow row = dt.NewRow();
                    row["class_name"] = "请选择";
                    row["class_id"] = 0;
                    dt.Rows.InsertAt(row, 0);

                    foreach (DataRow dr in dt.Rows)
                    {
                        if ((int)dr["class_id"] != 0) // 跳过默认项
                        {
                            string originalClassName = (string)dr["class_name"];
                            string originalGradeName = (string)dr["grade_name"];
                            dr["class_name"] = originalClassName + "----" + originalGradeName;
                        }
                    }
                    //绑定数据
                    comboBox_class.DataSource = dt;
                    comboBox_class.DisplayMember = "class_name";
                    comboBox_class.ValueMember = "class_id";

                    //默认选择0
                    comboBox_class.SelectedIndex = 0;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查找失败" + ex.Message, "提示");
                }
                connection.Close();
            }
        }

        private void comboBox_class_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void button_listStudent_Click(object sender, EventArgs e)
        {
            //查询学生数据
            initStudent();
        }

        //批量删除
        private void button_delete_Click(object sender, EventArgs e)
        {
            List<int> studentIdsToDelete = new List<int>();

            // 获取所有行并收集需要删除的学生ID
            for (int i = dgv_listStudent.Rows.Count - 1; i >= 0; i--)
            {
                DataGridViewRow row = dgv_listStudent.Rows[i];
                // 检查CheckBox是否被选中
                if (row.Cells["Column_checkBox"].Value != null && (bool)row.Cells["Column_checkBox"].Value)
                {
                    if (row.Cells["student_id"].Value != null)
                    {
                        int studentId = Convert.ToInt32(row.Cells["student_id"].Value);
                        studentIdsToDelete.Add(studentId);
                    }
                    else
                    {
                        MessageBox.Show("学生 ID 缺失，请检查数据完整性！");
                    }
                }
            }

            // 如果有学生ID需要删除，则显示确认消息
            if (studentIdsToDelete.Count > 0)
            {
                DialogResult result = MessageBox.Show("确认删除所选学生信息吗？", "确认删除", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (result == DialogResult.Yes)
                {
                    foreach (int studentId in studentIdsToDelete)
                    {
                        DeleteStudent(studentId);
                    }
                    // 删除完成后更新界面
                    initStudent();
                    //删除完成后提示
                    MessageBox.Show("删除成功！", "提示");
                }
            }
        }


        // 删除学生的方法
        private void DeleteStudent(int studentId)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["UserManagementConnectionString"].ConnectionString;
            string deleteSql = "DELETE FROM student WHERE student_id = @studentId";

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    using (MySqlCommand command = new MySqlCommand(deleteSql, connection))
                    {
                        command.Parameters.AddWithValue("@studentId", studentId);
                        int rowsAffected = command.ExecuteNonQuery();
                        if (rowsAffected > 0)
                        {
                            // 成功删除的信息已经在RefreshDataGridView中提示了
                        }
                        else
                        {
                            MessageBox.Show("未找到指定的学生记录！");
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("删除失败！" + ex.Message);
                }
            }
        }
        /**
         * 子页面铺满整个父页面mdi区域
         */
        private void pullMdiForm(Form form)
        {
            form.WindowState = FormWindowState.Normal;

            form.MdiParent = this.ParentForm;

            form.MaximizeBox = false;

            form.Dock = DockStyle.Fill;
        }

        /**
         * 导出数据
         * 将dgv查询到的数据导出到excel
         */
        private void button_exportData_Click(object sender, EventArgs e)
        {
            //将dgv查询到的数据导出到excel
            eportToExcel(dgv_listStudent);
        }

        private void eportToExcel(DataGridView dgv)
        {
            // 设置为非商业用途
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            // 创建一个新的excel包
            using (var package = new ExcelPackage())
            {
                // 添加一个新的工作表
                var worksheet = package.Workbook.Worksheets.Add("Sheet1");

                // 将DataGridView的列标题添加到Excel的第一行（如果需要保留后几列的标题）
                for (int i = 3; i < dgv.Columns.Count; i++)
                {
                    worksheet.Cells[1, i - 2].Value = dgv.Columns[i].HeaderText; // 减去2以适应跳过的三列
                }

                // 遍历DataGridView的每一行，将数据写入Excel
                for (int rowIndex = 0; rowIndex < dgv.Rows.Count; rowIndex++)
                {
                    var row = dgv.Rows[rowIndex];
                    if (row.IsNewRow) continue; // 跳过空白行

                    // 从第四列开始写入数据
                    for (int colIndex = 3; colIndex < dgv.Columns.Count; colIndex++)
                    {
                        worksheet.Cells[rowIndex + 2, colIndex - 2].Value = row.Cells[colIndex].Value; // 减去2以适应跳过的三列
                    }
                }

                // 使用 SaveFileDialog 让用户选择保存位置和文件名
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
                saveFileDialog.Title = "保存Excel文件";
                saveFileDialog.ShowDialog(); // 显示对话框

                if (saveFileDialog.FileName != "")
                {
                    // 用户选择了保存位置
                    FileInfo file = new FileInfo(saveFileDialog.FileName);
                    package.SaveAs(file);

                    MessageBox.Show("数据已成功导出到Excel!");
                }
            }
        }



        //单例化
        //private static ListStudentForm listStudentForm = null;
        //public static ListStudentForm instance
        //    {
        //        get
        //        {
        //            if (listStudentForm == null || listStudentForm.IsDisposed)
        //            {
        //                listStudentForm = new ListStudentForm();
        //            }
        //            return listStudentForm;
        //        }
        //    }
        //}
    }
}
